package com.ruslan.magaz.repository;


import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.ruslan.magaz.entity.Info;
import com.ruslan.magaz.entity.InfoName;
import com.ruslan.magaz.entity.User;


@Repository
public interface UserRepository extends JpaRepository<User, Integer> {
	
	@Query("SELECT DISTINCT LOWER(SUBSTRING(u.lastName, 1, 1)) as letters FROM User u ORDER BY letters")
    List<String> getFirstLetters();
    
	@Query("SELECT u FROM User u WHERE LOWER(SUBSTRING(u.lastName, 1, 1)) = (?1)")
    Page<User> findAllForLetter(String Letter, Pageable page);
	
	@Query("SELECT count(u) FROM User u WHERE u.firstName = (?1) AND u.middleName = (?2) AND u.lastName = (?3)")
	int checkSameUser(String firstName, String middleName, String lastName);
	
	@Query("SELECT count(u) FROM User u WHERE u.firstName = (?2) AND u.middleName = (?3) AND u.lastName = (?4) AND u.id != (?1)")
    int countIdenticalUsers(int id, String firstName, String middleName, String lastName);
}
